package com.baizhi.test;

import com.baizhi.Application;
import com.baizhi.dao.GurnDao;
import com.baizhi.entity.Gurn;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class test2 {
    @Resource
    private GurnDao gurnDao;
    @Test
    public  void testHSSF() throws Exception {
        String[] titles = {"上师编号","上师名字","上师图⽚地址","上师法号","上师状态"};
        //创建文件对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建xls表
        HSSFSheet sheet = workbook.createSheet("guru");
        //行数，从0开始
        HSSFRow titleRow = sheet.createRow(0);
        //列数，从0开始
        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);

        }


        /**
         * 写⼊上师数据
         */
        //从第二行开始
        List<Gurn> gurns = gurnDao.selectAll(null);

        for (int i = 0; i < gurns.size(); i++) {
            HSSFRow row = sheet.createRow(i+1);
                  
         // 将上师数据写⼊到⾏中
            Gurn gurn = gurns.get(i);
            //循环写入
//            HSSFCell idCell = row.createCell(0);
//            idCell.setCellValue(gurn.getGuruId());
//            row.createCell(1).setCellValue(gurn.getGuruName());
//            row.createCell(2).setCellValue(gurn.getGuruImage());
//
//            row.createCell(3).setCellValue(gurn.getGuruNickName());
//
//            row.createCell(4).setCellValue(gurn.getGuruStatus());
            /*
            * 使用反射优化数据的导出
            * */
            //获取类对象
            Class<? extends Gurn> gurnClass = gurn.getClass();
            //获取属性
            Field[] fields = gurnClass.getDeclaredFields();

            for (int j = 0; j < fields.length; j++) {
                //创建单元格
                HSSFCell cell = row.createCell(j);
                // 获取属性对象对应的属性值
                Field field = fields[j];
                field.setAccessible(true);
                Object value = field.get(gurn);
                // 将属性值写⼊到单元格 写出的时候⽤的全部都是String 那么读取的时候能够 getNumericCellValue() 吗？
                cell.setCellValue(value.toString());
            }
        }
        workbook.write(new FileOutputStream("E://guru.xls"));
    }

    @Test
    public void insertTest() throws Exception{
        // 1.通过流读取Excel⽂件
        FileInputStream inputStream = new FileInputStream("E://guru.xls");
        // 2.通过poi解析流 HSSFWorkbook 处理流得到的对象中 就封装了Excel⽂件所有的数据
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 3.从⽂件中获取表对象 getSheetAt通过下标获取
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 4.从表中获取到⾏数据 从第⼆⾏开始 到 最后⼀⾏ getLastRowNum()获取最后⼀⾏的下标
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <=lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
// 从⾏中获取数据
            /**
             * getNumericCellValue() 获取数字
             * getStringCellValue 获取String
             */

            String name = row.getCell(1).getStringCellValue();
            String img = row.getCell(2).getStringCellValue();
            String niname = row.getCell(3).getStringCellValue();
            double status = row.getCell(4).getNumericCellValue();
    // 封装到对象中
            Gurn gurn1 = new Gurn();

            gurn1.setGuruName(name);
            gurn1.setGuruImage(img);
            gurn1.setGuruNickName(niname);
            gurn1.setGuruStatus((int) status);
            System.out.println(gurn1);
            gurnDao.insert(gurn1);
        }
    }
}
